package com.zzzzzz.content.picitem;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.zzzzzz.common.GlobalConstants;

@Repository
public class PicItemDao {
	
	@Resource
	private JdbcTemplate jdbcTemplate;
	
	private static final String ADD = 						"INSERT INTO picitem(pictext_id, user_id, add_dt, upd_dt, status) VALUES(?, ?, ?, ?, ?)";
	private static final String EDIT =						"update picitem set text_s = ?, is_cover = ?, upd_dt = ? where id = ? and user_id = ?";
	private static final String EDIT_STATUS = 				"update picitem set status = ?, upd_dt = ? where id = ? and user_id = ?";
	
	private final static String EDIT_LOOKS = 				"update picitem set looks = looks + ? where id = ?";
	private final static String EDIT_REPLIES = 				"update picitem set replies = replies + ? where id = ?";
	private final static String EDIT_LIKES = 				"update picitem set likes = likes + ? where id = ?";
	private final static String EDIT_ISCOVER_BY_PICTEXTID = "update picitem set is_cover = ? where pictext_id = ? and is_cover = ? and user_id = ?";
	private final static String EDIT_ISCOVER_BY_ID = 		"update picitem set is_cover = ? where id = ? and user_id = ?";
	private final static String EDIT_SEQ_NB = 				"update picitem set seq_nb = ? where id = ? and user_id = ?";
	
	private final static String FIND_BY_ID = 				"select id, pictext_id, text_s, is_cover, user_id, add_dt, upd_dt, status, looks, replies, likes from picitem where id = ?";
	private final static String FIND_VIEW_BY_ID = 			"select p.id, p.pictext_id, p.text_s, p.is_cover, p.user_id, p.add_dt, p.upd_dt, p.status, p.looks, p.replies, p.likes, u.username, ifnull(u.website, u.id) as website from picitem p inner join user u on p.user_id = u.id where p.id = ?";
	
	private final static String FIND_BY_GT_ID = 			"select id, text_s, is_cover from picitem where pictext_id = ? and id >= ? and status = ?";
	
	private final static String FIND_BY_PICTEXTID = 		"select id, text_s, is_cover from picitem where pictext_id = ? and status = ? order by ifnull(seq_nb, 999999) asc, id asc limit ?, ?";
	private final static String FIND_BY_PICTEXTID_COUNT = 	"select count(1) as count from picitem where pictext_id = ? and status = ?";
	
	private final static String FIND_ID_BY_PICTEXTID = 		"select id from picitem where pictext_id = ? and status = ? order by ifnull(is_cover, 0) desc, ifnull(seq_nb, 999999) asc, id asc limit ?, ?";
	private final static String FIND_ALL_ID_BY_PICTEXTID = 	"select id from picitem where pictext_id = ? and status = ? order by ifnull(seq_nb, 999999) asc, id asc";
	
	public Long add(Long picTextId, Long userId) {
		jdbcTemplate.update(ADD, picTextId, userId, new Date(), new Date(), GlobalConstants.STATUS.NORMAL);
		return jdbcTemplate.queryForLong("SELECT @@IDENTITY");
	}

	public void edit(PicItem picItem) {
		jdbcTemplate.update(EDIT, picItem.getTextS(), picItem.getIsCover(), new Date(), picItem.getId(), picItem.getUserId());
	}
	
	public void editStatus(Long id, Integer status, Long userId) {
		jdbcTemplate.update(EDIT_STATUS, status, new Date(), id, userId);
	}

	public void editLooks(Long id, Integer looksPlus){
		jdbcTemplate.update(EDIT_LOOKS, looksPlus, id);
	}
	
	public void editLikes(Long id, Integer likesPlus){
		jdbcTemplate.update(EDIT_LIKES, likesPlus, id);
	}
	
	public void editReplies(Long id, Integer repliesPlus){
		jdbcTemplate.update(EDIT_REPLIES, repliesPlus, id);
	}
	
	public void editIsCoverByPicTextId(Boolean isCoverNew, Long picTextId, Boolean isCoverOld, Long userId){
		jdbcTemplate.update(EDIT_ISCOVER_BY_PICTEXTID, isCoverNew, picTextId, isCoverOld, userId);
	}
	public void editIsCoverById(Long id, Boolean isCover, Long userId){
		jdbcTemplate.update(EDIT_ISCOVER_BY_ID, isCover, id, userId);
	}
	
	public void editSeqNb(Integer seqNb, Long id, Long userId) {
		jdbcTemplate.update(EDIT_SEQ_NB, seqNb, id, userId);
	}
	
	public PicItem findById(Long id){
		return jdbcTemplate.queryForObject(FIND_BY_ID, new RowMapper<PicItem>(){
			public PicItem mapRow(ResultSet rs, int rowNum) throws SQLException {
				PicItem picItem = new PicItem();
				picItem.setId(rs.getLong("id"));
				picItem.setPicTextId(rs.getLong("pictext_id"));
				picItem.setTextS(rs.getString("text_s"));
				picItem.setIsCover(rs.getBoolean("is_cover"));
				picItem.setUserId(rs.getLong("user_id"));
				picItem.setAddDt(rs.getTimestamp("add_dt"));
				picItem.setUpdDt(rs.getTimestamp("upd_dt"));
				picItem.setStatus(rs.getInt("status"));
				picItem.setLooks(rs.getInt("looks"));
				picItem.setReplies(rs.getInt("replies"));
				picItem.setLikes(rs.getInt("likes"));
				return picItem;
			}
		}, id);
	}
	
	public PicItemView findViewById(Long id){
		return jdbcTemplate.queryForObject(FIND_VIEW_BY_ID, new RowMapper<PicItemView>(){
			public PicItemView mapRow(ResultSet rs, int rowNum) throws SQLException {
				PicItemView picItemView = new PicItemView();
				picItemView.setId(rs.getLong("id"));
				picItemView.setPicTextId(rs.getLong("pictext_id"));
				picItemView.setTextS(rs.getString("text_s"));
				picItemView.setIsCover(rs.getBoolean("is_cover"));
				picItemView.setUserId(rs.getLong("user_id"));
				picItemView.setAddDt(rs.getTimestamp("add_dt"));
				picItemView.setUpdDt(rs.getTimestamp("upd_dt"));
				picItemView.setLooks(rs.getInt("looks"));
				picItemView.setReplies(rs.getInt("replies"));
				picItemView.setLikes(rs.getInt("likes"));
				picItemView.setUsername(rs.getString("username"));
				picItemView.setWebsite(rs.getString("website"));
				return picItemView;
			}
		}, id);
	}
	
	protected class PicItemListViewRowMapper implements RowMapper<PicItemListView> {
		public PicItemListView mapRow(ResultSet rs, int rowNum) throws SQLException {
			PicItemListView picItemListView = new PicItemListView();
			picItemListView.setId(rs.getLong("id"));
			picItemListView.setTextS(rs.getString("text_s"));
			picItemListView.setIsCover(rs.getBoolean("is_cover"));
			return picItemListView;
		}
	}
	
	public List<PicItemListView> findByGtId(Long picTextId, Long id){
		return jdbcTemplate.query(FIND_BY_GT_ID, new PicItemListViewRowMapper(), picTextId, id, GlobalConstants.STATUS.NORMAL);
	}
	
	public List<PicItemListView> findByPicTextId(Long picTextId, int start, int offset){
		return jdbcTemplate.query(FIND_BY_PICTEXTID, new PicItemListViewRowMapper(), picTextId, GlobalConstants.STATUS.NORMAL, start, offset);
	}
	
	public long findByPicTextIdCount(Long picTextId){
		return jdbcTemplate.queryForLong(FIND_BY_PICTEXTID_COUNT, picTextId, GlobalConstants.STATUS.NORMAL);
	}
	
	public List<Long> findIdByPicTextId(Long picTextId, int start, int offset){
		return jdbcTemplate.queryForList(FIND_ID_BY_PICTEXTID, Long.class, picTextId, GlobalConstants.STATUS.NORMAL, start, offset);
	}
	
	public List<Long> findAllIdByPicTextId(Long picTextId){
		return jdbcTemplate.queryForList(FIND_ALL_ID_BY_PICTEXTID, Long.class, picTextId, GlobalConstants.STATUS.NORMAL);
	}
	
}
